package cn.yunhe.dao;

import cn.yunhe.model.PageBean;
import cn.yunhe.model.UserInfo;
import cn.yunhe.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;

/**
 * Created by Administrator on 2017/9/12.
 */
public class UserDao {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    public List<UserInfo> queryAll() {
        List<UserInfo> list = new ArrayList<UserInfo>();
        try {
            conn = DButil.getConn();
            String sql = "select * from user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                UserInfo c = new UserInfo();
                c.setId(rs.getInt(1));
                c.setUsername(rs.getString(2));
                c.setPassword(rs.getString(3));
                c.setSex(rs.getString(4));
                c.setAge(String.valueOf(rs.getInt(5)));
                c.setXueli(rs.getString(6));
                c.setAddress(rs.getString(7));

                list.add(c);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public int addUser(UserInfo info) {
        try {
            conn = DButil.getConn();
            String sql = "insert into user(username,password,sex,age,xueli,address) values(?,?,?,?,?,?);";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, info.getUsername());
            pstmt.setString(2, info.getPassword());
            pstmt.setString(3, info.getSex());
            pstmt.setInt(4, Integer.parseInt(info.getAge()));
            pstmt.setString(5, info.getXueli());
            pstmt.setString(6, info.getAddress());

            return pstmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    public int deleteUser(int id) {
        try {
            conn = DButil.getConn();
            String sql = "delete from user where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            return pstmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    public int updateUser(UserInfo info, int id) {
        try {
            conn = DButil.getConn();
            String sql = "update user set username=?,password=?,sex=?,age=?,xueli=?,address=? where id=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, info.getUsername());
            pstmt.setString(2, info.getPassword());
            pstmt.setString(3, info.getSex());
            pstmt.setInt(4, Integer.parseInt(info.getAge()));
            pstmt.setString(5, info.getXueli());
            pstmt.setString(6, info.getAddress());
            pstmt.setInt(7, id);


            return pstmt.executeUpdate();

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * 分页
     *
     * @param pageBean
     * @return
     * @throws Exception
     */
    public ResultSet userList(PageBean pageBean) {

        try {
            String sql = "select * from user order by id DESC limit ?,?";
            conn = DButil.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pageBean.getStart());
            pstmt.setInt(2, pageBean.getRows());
            rs = pstmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }

    public int userCount() {
        conn = DButil.getConn();
        int count = 0;
        try {
            String sql = "select count(1) as total from user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                count = rs.getInt("total");
            } else {
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


}
